<?php
class JobsManage
{
    public static function getJobslistForPageIndex($dataPerpage, $lang, $location) {
        
        $ConLocat = $location == 1 ?"ProvinceID = 1": "ProvinceID != 1";
        $langUpper = strtoupper($lang);
        $sql = "SELECT *, Position{$langUpper} AS Position, Location{$langUpper} AS Location, Descript{$langUpper} AS Descript, GetNameProvinceByID(ProvinceID, '{$lang}') AS ProvinceName
            FROM jobs
            WHERE State = 'O' AND DeleteDate IS NULL 
            AND {$ConLocat}
            ORDER BY CreateDate DESC, JobID DESC
            LIMIT 0,{$dataPerpage}";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        return $command->queryAll();
    }
    
    public static function getJobsUrgent($lang){
        $langUpper = strtoupper($lang);
        $sql = "SELECT *, Position{$langUpper} AS Position, Location{$langUpper} AS Location, Descript{$langUpper} AS Descript, GetNameProvinceByID(ProvinceID, '{$lang}') AS ProvinceName
            FROM jobs
            WHERE State IN ('O', 'U') AND Urgent = 'U' AND DeleteDate IS NULL 
            ORDER BY CreateDate DESC, JobID DESC
            LIMIT 0,10";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        return  $command->queryAll();
    }
    
    public static function getJobslist($page, $dataPerpage, $lang, $isAdmin) {
        
        $status = $isAdmin?"'O','U','C'":"'O','U'";//ปรับสเตตัส ให้เห็นข้อมูลตามสิทธ์
        $sql = "SELECT *
            FROM jobs
            WHERE State IN ({$status}) AND DeleteDate IS NULL 
            ORDER BY CreateDate DESC, JobID DESC";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $maxPage =  ceil($command->execute()/$dataPerpage);
        
        $langUpper = strtoupper($lang);
        $startRow = $dataPerpage*($page-1);
        $sql = "SELECT *, Position{$langUpper} AS Position, Location{$langUpper} AS Location, Descript{$langUpper} AS Descript, GetNameProvinceByID(ProvinceID, '{$lang}') AS ProvinceName
            FROM jobs
            WHERE State IN ({$status}) AND DeleteDate IS NULL 
            ORDER BY CreateDate DESC, JobID DESC
            LIMIT {$startRow},{$dataPerpage}";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $data =  $command->queryAll();
        return array(
            'MaxPage' => $maxPage,
            'data' => $data
        );
    }
    
    public static function getJobslistByKeyword($Keyword, $JobID, $Province, $page, $dataPerPage, $isAdmin){
        
        $status = $isAdmin?"'O','U','C'":"'O','U'";//ปรับสเตตัส ให้เห็นข้อมูลตามสิทธ์
        $sql = "SELECT *
            FROM jobs
            WHERE State IN ({$status}) AND DeleteDate IS NULL 
                AND ((JobID = :JobID OR '' = :JobID) 
                        AND (ProvinceID = :ProvinceID OR 0 = :ProvinceID)
                        AND (LOWER(PositionEN) LIKE :Keyword
                            OR LOWER(PositionTH) LIKE :Keyword
                            OR LOWER(DescriptEN) LIKE :Keyword
                            OR LOWER(DescriptTH) LIKE :Keyword ))
            ORDER BY CreateDate DESC, JobID DESC";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':JobID', $JobID);
        $command->bindParam(':ProvinceID', $Province);
        $Keyword = '%'.strtolower($Keyword).'%';
        $command->bindParam(':Keyword', $Keyword);
        $maxPage =  ceil($command->execute()/$dataPerPage);
        
        $startRow = $dataPerPage*($page-1);
        $sql = "SELECT *
            FROM jobs
            WHERE State IN ({$status}) AND DeleteDate IS NULL 
                AND ((JobID = :JobID OR '' = :JobID) 
                        AND (ProvinceID = :ProvinceID OR 0 = :ProvinceID)
                        AND (LOWER(PositionEN) LIKE :Keyword
                            OR LOWER(PositionTH) LIKE :Keyword
                            OR LOWER(DescriptEN) LIKE :Keyword
                            OR LOWER(DescriptTH) LIKE :Keyword ))
            ORDER BY CreateDate DESC, JobID DESC
            LIMIT {$startRow},{$dataPerPage}";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':JobID', $JobID);
        $command->bindParam(':ProvinceID', $Province);
        $command->bindParam(':Keyword', $Keyword);
        $data =  $command->queryAll();
        return array(
            'MaxPage' => $maxPage,
            'data' => $data
        );
        
    }
    
    public static function insertJob($post) {
        if( $post['state'] == "O" ) {
            $post['urgent'] = "N";
        } else {
            $post['urgent'] = "U";
            $post['state']  = "O"; 
        }
        $jobdesEN = addslashes(str_replace("\"","'",$post['jobdesEN']));
        $jobdesTH = addslashes(str_replace("\"","'",$post['jobdesTH']));
        $sql = "INSERT INTO jobs(PositionEN,PositionTH,Sex,Age,Exp,Salary,ProvinceID,LocationEN,LocationTH,DescriptEN,DescriptTH,State,Urgent,CreateDate) ";
        $sql.= "VALUES('$post[positionEN]', ";
        $sql.= "'$post[positionTH]',";
        $sql.= "'$post[gender]',";
        $sql.= "'$post[age]',";
        $sql.= "'$post[exp]',";
        $sql.= "'$post[salary]',";
        $sql.= "$post[provinceID],";
        $sql.= "'$post[locationEN]',";
        $sql.= "'$post[locationTH]',";
        $sql.= "'".$jobdesEN."',";
        $sql.= "'".$jobdesTH."',";
        $sql.= "'$post[state]',";
        $sql.= "'$post[urgent]',";
        $sql.= "'$post[createdate]');";
        $connection = Yii::app()->db; 
        $command = $connection->createCommand($sql);
        return $command->execute();
    }
    
    public static function updateJob($post,$id) {
        if($post['state'] == "O") {
            if($post['urgent'] == "U") {
                $post['urgent'] = "N";
            }
        }
        $jobdesEN = addslashes(str_replace("\"","'",$post['jobdesEN']));
        $jobdesTH = addslashes(str_replace("\"","'",$post['jobdesTH']));
        $sql = "UPDATE jobs SET ";
        $sql.= "PositionEN='$post[positionEN]',";
        $sql.= "PositionTH='$post[positionTH]',";
        $sql.= "Sex='$post[gender]',";
        $sql.= "Age='$post[age]',";
        $sql.= "Exp='$post[exp]',";
        $sql.= "Salary='$post[salary]',";
        $sql.= "ProvinceID=$post[provinceID],";
        $sql.= "LocationEN='$post[locationEN]',";
        $sql.= "LocationTH='$post[locationTH]',";
        $sql.= "DescriptEN='".$jobdesEN."',";
        $sql.= "DescriptTH='".$jobdesTH."',";
        $sql.= "State='$post[state]',";
        $sql.= "Urgent='$post[urgent]',";
        $sql.= "CreateDate='$post[createdate]' ";
        $sql.= "WHERE JobID = $id;";
        $connection = Yii::app()->db; 
        $command = $connection->createCommand($sql);
        $command->bindParam(':id',$id);
        return $command->execute();
    }
    
    public static function changeUrgent($ID, $Urgent){
        $sql="UPDATE jobs SET Urgent = :urgentChange WHERE JobID = :ID;";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':ID', $ID);
        $command->bindParam(':urgentChange', $Urgent);
        return $command->execute();
    }
    
    public static function editStatusJob($ID, $Status){
        $sql="UPDATE jobs SET State = :state WHERE JobID = :ID;";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':ID', $ID);
        $command->bindParam(':state', $Status);
        return $command->execute();
    }
    
    public static function find($key, $col) {
        $sql = "SELECT * FROM jobs WHERE $col like '%$key%' ";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        return $command->queryAll();
    }
    
    public static function deleteJOB($id) {
        $sql = "UPDATE jobs SET DeleteDate = CURDATE(), State = 'C' WHERE JobID = :id ;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':id',$id);
        return $command->execute();
    }
    
    public static function GetInfoByID($jobID,$lang) {
        $langUpper = strtoupper($lang);
        $sql="SELECT *, Position{$langUpper} AS Position, Location{$langUpper} AS Location, Descript{$langUpper} AS Descript, GetNameProvinceByID(ProvinceID,:lang) AS ProvinceNAME
            FROM jobs WHERE jobID = :ID;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(":lang",$lang);
        $command->bindParam(":ID",$jobID);
        return $command->queryRow();
    }
    
    public static function GetProvinceName($jobID,$lang) {
        $sql="SELECT GetNameProvinceByID(ProvinceID,:lang) AS ProvinceNAME FROM jobs WHERE jobID = :ID;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':ID', $jobID);
        $command->bindParam(':lang', $lang);
        return $command->queryRow();
    }
    
    public static function GetMaxPosition() {
        $sql = "SELECT COUNT(jobID) as MAX FROM jobs where DeleteDate IS NULL;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $result = $command->queryRow();
        return $result['MAX'];
    }
    
    public static function GetDataPerPage($page,$dataPerpage) {
        $page = ($page - 1) * ($dataPerpage);
        $sql="SELECT * FROM jobs WHERE DeleteDate IS NULL LIMIT :startRow,:dataPerpage;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':startRow',$page);
        $command->bindParam(':dataPerpage', $dataPerpage);
        return $command->queryAll();
    }
    
    public static function MapGender($sex , $lang="") {
        switch($sex) {
            case "M" : return $lang=="en"?"Male":"ผู้ชาย";
                break;
            case "F" : return $lang=="en"?"Female":"ผู้หญิง";
                break;
            default :  return $lang=="en"?"Undefined":"ไม่จำกัดเพศ";
        }
    }
    
    public static function MapState($state , $lang="") {
        switch($state) {
            case "O" : return $lang=="en"?"Open":"เปิดรับสมัคร";
                break;
            case "U" : return $lang=="en"?"Urgent":"ต้องการด่วน";
                break;
            case "C" :  return $lang=="en"?"Close":"ปิดรับสมัคร";
                break;
        }
    }
    
    public static function isNewsJob($jobDate){
        $day = GM::DateDiff(date("Y-m-d"), $jobDate);
        if($day<8){
            return true;
        }
        return false;
    }
}
